﻿Imports System.Text

Namespace StatementSources
    Public Class CreateExaminationStatementSource
        Inherits BaseStatementSource

        Public Sub New(entities As DataTable, createdBy As Integer)
            Dim stringBuilder As New StringBuilder()

            Dim counter As Integer = 0
            For Each entity As DataRow In entities.Rows
                stringBuilder.Append("INSERT [dbo].[Examinations] ")
                stringBuilder.Append("([ScheduleId],[ExaminationTypeId],[GroupId],[StudentCode],[Grade],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm]) ")
                stringBuilder.Append("VALUES ")
                stringBuilder.Append(String.Format("(@ScheduleId{0},@ExaminationTypeId{0},@GroupId{0},@StudentCode{0},@Grade{0}", counter))
                stringBuilder.Append(",@CreatedBy,GETDATE(),@CreatedBy,GETDATE());")

                Parameters.AddWithValue(String.Format("@ScheduleId{0}", counter), Convert.ToInt32(entity("ScheduleId")))
                Parameters.AddWithValue(String.Format("@ExaminationTypeId{0}", counter), Convert.ToInt32(entity("ExaminationTypeId")))
                Parameters.AddWithValue(String.Format("@GroupId{0}", counter), Convert.ToInt32(entity("GroupId")))
                Parameters.AddWithValue(String.Format("@StudentCode{0}", counter), Convert.ToInt32(entity("StudentCode")))
                Parameters.AddWithValue(String.Format("@Grade{0}", counter), Convert.ToDecimal(entity("Grade")))

                counter += 1
            Next

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@CreatedBy", createdBy)
        End Sub

    End Class

    Public Class ReadAllExaminationStatementSource
        Inherits BaseStatementSource

        Public Sub New()
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [ExaminationId],[ScheduleId],[ExaminationTypeId],[GroupId],[StudentCode],[Grade],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Examinations];")

            Sql = stringBuilder.ToString()
        End Sub

        Public Sub New(studentCode As String, scheduleId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT e.[ExaminationId],e.[ScheduleId],e.[ExaminationTypeId],e.[GroupId],e.[StudentCode],e.[Grade],e.[CreatedBy],e.[CreatedDttm],e.[ModifiedBy],e.[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[ExaminationTypes] et ")
            stringBuilder.Append("INNER JOIN [dbo].[Examinations] e ")
            stringBuilder.Append("ON e.[ExaminationTypeId]=et.[ExaminationTypeId] ")
            stringBuilder.Append("WHERE et.[GroupId]=0 ")
            stringBuilder.Append("AND e.[ScheduleId]=@ScheduleId ")
            stringBuilder.Append("AND e.[StudentCode]=@StudentCode ")
            stringBuilder.Append("AND e.[GroupId]=et.[ExaminationTypeId];")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@StudentCode", studentCode)
            Parameters.AddWithValue("@ScheduleId", scheduleId)
        End Sub

        Public Sub New(studentCode As String, scheduleId As Integer, groupId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [ExaminationId],[ScheduleId],[ExaminationTypeId],[GroupId],[StudentCode],[Grade],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Examinations] ")
            stringBuilder.Append("WHERE [ScheduleId]=@ScheduleId ")
            stringBuilder.Append("AND [GroupId]=@GroupId ")
            stringBuilder.Append("AND [StudentCode]=@StudentCode; ")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@StudentCode", studentCode)
            Parameters.AddWithValue("@ScheduleId", scheduleId)
            Parameters.AddWithValue("@GroupId", groupId)
        End Sub

        Public Sub New(studentCode As String, scheduleId As Integer, groupId As Integer, examinationTypeId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [ExaminationId],[ScheduleId],[ExaminationTypeId],[GroupId],[StudentCode],[Grade],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Examinations] ")
            stringBuilder.Append("WHERE [ScheduleId]=@ScheduleId ")
            stringBuilder.Append("AND [ExaminationTypeId]=@ExaminationTypeId ")
            stringBuilder.Append("AND [GroupId]=@GroupId ")
            stringBuilder.Append("AND [StudentCode]=@StudentCode; ")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@StudentCode", studentCode)
            Parameters.AddWithValue("@ScheduleId", scheduleId)
            Parameters.AddWithValue("@GroupId", groupId)
            Parameters.AddWithValue("@ExaminationTypeId", examinationTypeId)
        End Sub

    End Class

    Public Class ReadByExaminationIdStatementSource
        Inherits BaseStatementSource

        Public Sub New(examinationId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [ExaminationId],[ScheduleId],[ExaminationTypeId],[GroupId],[StudentCode],[Grade],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Examinations] ")
            stringBuilder.Append("WHERE [ExaminationId]=@ExaminationId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@ExaminationId", examinationId)
        End Sub

    End Class

    Public Class UpdateExaminationStatementSource
        Inherits BaseStatementSource

        Public Sub New(entities As DataTable, modifiedBy As Integer)
            Dim stringBuilder As New StringBuilder()

            Dim counter As Integer = 0
            For Each entity As DataRow In entities.Rows
                stringBuilder.Append("UPDATE [dbo].[Examinations] SET ")
                stringBuilder.Append(String.Format("[ScheduleId]=ISNULL(@ScheduleId{0},[ScheduleId]),", counter))
                stringBuilder.Append(String.Format("[ExaminationTypeId]=ISNULL(@ExaminationTypeId{0},[ExaminationTypeId]),", counter))
                stringBuilder.Append(String.Format("[GroupId]=ISNULL(@GroupId{0},[GroupId]),", counter))
                stringBuilder.Append(String.Format("[StudentCode]=ISNULL(@StudentCode{0},[StudentCode]),", counter))
                stringBuilder.Append(String.Format("[Grade]=ISNULL(@Grade{0},[Grade]),", counter))
                stringBuilder.Append("[ModifiedBy]=@ModifiedBy,")
                stringBuilder.Append("[ModifiedDttm]=GETDATE() ")
                stringBuilder.Append(String.Format("WHERE [ExaminationId]=@ExaminationId{0};", counter))

                Parameters.AddWithValue(String.Format("@ExaminationId{0}", counter), Convert.ToInt32(entity("ExaminationId")))
                Parameters.AddWithValue(String.Format("@ScheduleId{0}", counter), Convert.ToInt32(entity("ScheduleId")))
                Parameters.AddWithValue(String.Format("@ExaminationTypeId{0}", counter), Convert.ToInt32(entity("ExaminationTypeId")))
                Parameters.AddWithValue(String.Format("@GroupId{0}", counter), Convert.ToInt32(entity("GroupId")))
                Parameters.AddWithValue(String.Format("@StudentCode{0}", counter), Convert.ToInt32(entity("StudentCode")))
                Parameters.AddWithValue(String.Format("@Grade{0}", counter), Convert.ToDecimal(entity("Grade")))

                counter += 1
            Next

            Sql = StringBuilder.ToString()

            Parameters.AddWithValue("@ModifiedBy", modifiedBy)
        End Sub

    End Class

    Public Class DeleteExaminationStatementSource
        Inherits BaseStatementSource

        Public Sub New(examinationId As Integer)
            Sql = "DELETE FROM [dbo].[Examinations] WHERE [ExaminationId]=@ExaminationId;"
            Parameters.AddWithValue("@ExaminationId", examinationId)
        End Sub

    End Class

End Namespace

